if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetCOIXML]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetCOIXML]
GO
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
/******************************************************************    
* Name:   GetCOIXML    
* Purpose:  Retrieves the COI with the specified Id    
*    
* PARAMETERS    
* Name         Description         
* -------------     -------------------------------------------    
* @coiId     COI Id    
******************************************************************/    
    
CREATE Procedure [dbo].[GetCOIXML] (    
  @coiId INT    
)    
AS    
BEGIN    
    
 SET NOCOUNT ON    
     
 SELECT COI.Id AS "@Id", IsNull(ADMP.OutsideMember, 0) as OutsideMember, year(COI.DateCreated) as [Year],     
   ADMP.LastName + ', ' + ADMP.FirstName as [Name], convert(varchar, COI.DateSubmitted, 101) as DateSubmitted,     
   case COI.HasOutsideActivities when 1 then 'Y' when 0 then 'N' else '' end as HasOutsideActivities,     
   case COI.HasEquityInterests when 1 then 'Y' when 0 then 'N' else '' end as HasEquityInterests,     
   case COI.HasFamilyAssociations when 1 then 'Y' when 0 then 'N' else '' end as HasFamilyAssociations,  
   case COI.Royalty when 1 then 'Y' when 0 then 'N' else '' end as HasRoyalties,   
   case COI.OtherActivity when 1 then 'Y' when 0 then 'N' else '' end as OtherActivity,   
   (
    SELECT A.Value FROM ConflictOfInterest COI
	INNER JOIN COI_QuestionnaireAnswer_Map COIQAMap ON COIQAMap.COIId = COI.Id
	INNER JOIN QuestionnaireAnswer QA ON QA.Id = COIQAMap.QuestionnaireAnswerId
	INNER JOIN Answer A ON A.QuestionnaireAnswerId = QA.Id
	WHERE
	COI.Id = @coiId
	AND A.Name = 'PrimaryInstitution' 
	) AS 'PrimaryInstitution', 
   (
    SELECT DISTINCT SADMP.LastName + ', ' + SADMP.FirstName as [Supervisor]    
    FROM COI_Hierarchy_Map Map     
     LEFT JOIN AdmPerson SADMP ON SADMP.Id = Map.ParentId    
    WHERE COI.AdmPersonId = Map.ChildId  
 and Map.Removed = 0    
    ORDER BY Supervisor    
    FOR XML PATH('Supervisor'), ROOT('Supervisors'), TYPE    
   ),    
   (    
    SELECT Title, OverallStatus     
    FROM AgrAgreement Agr    
     INNER JOIN SvcEntity SE ON SE.Id = Agr.SvcEntityId    
     INNER JOIN SvcEntity_AdmStaffedPerson_Map Map ON Map.SvcEntityId = SE.Id    
    WHERE Map.AdmPersonId = COI.AdmPersonId    
     AND Agr.IsCurrent = 1    
     AND (Agr.OverallStatus like 'Pending%' OR Agr.OverallStatus like 'Fully Executed%' OR Agr.OverallStatus like 'Active' OR Agr.OverallStatus like 'Approved')    
    FOR XML PATH('Agreement'), ROOT('Agreements'), TYPE    
   ),
   (  
    SELECT RC.RefMeaning   
    FROM RefCode RC   
     INNER JOIN Answer A ON RC.RefCode = A.Value  
     INNER JOIN QuestionnaireAnswer QA ON QA.Id = A.QuestionnaireAnswerId  
     INNER JOIN QuestionnaireVersion QV ON QV.Id = QA.QuestionnaireVersionId  
     INNER JOIN Questionnaire Q ON Q.Id = QV.QuestionnaireId  
     INNER JOIN COI_QuestionnaireAnswer_Map MAP ON MAP.QuestionnaireAnswerId = QA.Id  
    WHERE MAP.COIId = COI.Id  
     AND Q.[Name] in ('Individual Disclosure', 'Trustee Disclosure')  
     AND A.Name = 'BoardList'  
    FOR XML PATH('Board'), ROOT('Boards'), TYPE  
   ),
   (  
    SELECT RC.RefMeaning   
    FROM RefCode RC   
     INNER JOIN Answer A ON RC.RefCode = A.Value  
     INNER JOIN QuestionnaireAnswer QA ON QA.Id = A.QuestionnaireAnswerId  
     INNER JOIN QuestionnaireVersion QV ON QV.Id = QA.QuestionnaireVersionId  
     INNER JOIN Questionnaire Q ON Q.Id = QV.QuestionnaireId  
     INNER JOIN COI_QuestionnaireAnswer_Map MAP ON MAP.QuestionnaireAnswerId = QA.Id  
    WHERE MAP.COIId = COI.Id  
     AND Q.[Name] in ('Individual Disclosure', 'Trustee Disclosure')  
     AND A.Name = 'BoardCommitteeList'  
    FOR XML PATH('BoardCommittee'), ROOT('BoardCommittees'), TYPE  
   ),    
   (    
    SELECT P.Title     
     FROM protocolstaff_map Map    
      INNER JOIN Protocol  P ON Map.ProtocolId = P.Id    
      INNER JOIN ProtocolOverallStatusType POST ON POST.Id = P.OverallStatusId    
     WHERE Map.admpersonid = COI.AdmPersonId      
      AND Map.IsCurrent = 1    
      AND POST.Name in ('Active', 'Pending', 'Submitted', 'Exempt', 'In Progress')    
    FOR XML PATH('Protocol'), ROOT('Protocols'), TYPE    
   ),    
   (     
    SELECT QA.Name, QA.Highlight,    
    (    
     SELECT A.[Name], A.[Value], (SELECT [IO].OptionText     
             FROM ItemOption [IO]    
              INNER JOIN QuestionnaireItem QI ON QI.Id = [IO].ItemId    
             WHERE QI.VersionId = QV.Id    
              AND QI.ItemName = A.[Name]    
              AND [IO].OptionValue = A.[Value]) AS [Text],    
            (SELECT RefMeaning    
             FROM RefCode    
             WHERE RefCode.RefCode = A.Value) AS [RefMeaning]    
     FROM Answer A     
     WHERE A.QuestionnaireAnswerId = QA.Id    
     FOR XML PATH('Answer'), ROOT('Answers'), TYPE    
    )    
    FROM QuestionnaireAnswer QA     
     INNER JOIN QuestionnaireVersion QV ON QV.Id = QA.QuestionnaireVersionId    
     INNER JOIN Questionnaire Q ON Q.Id = QV.QuestionnaireId    
     INNER JOIN COI_QuestionnaireAnswer_Map MAP ON MAP.QuestionnaireAnswerId = QA.Id    
    WHERE MAP.COIId = COI.Id    
    AND Q.[Name] = 'Your Outside Activities'    
    FOR XML PATH('OutsideActivity'), ROOT('OutsideActivities'), TYPE    
   ),    
   (     
    SELECT QA.Name, QA.Highlight,    
    (    
     SELECT A.[Name], A.[Value], (SELECT [IO].OptionText     
             FROM ItemOption [IO]    
              INNER JOIN QuestionnaireItem QI ON QI.Id = [IO].ItemId    
             WHERE QI.VersionId = QV.Id    
              AND QI.ItemName = A.[Name]    
              AND [IO].OptionValue = A.[Value]) AS [Text],    
            (SELECT RefMeaning    
             FROM RefCode    
             WHERE RefCode.RefCode = A.Value) AS [RefMeaning]    
     FROM Answer A     
     WHERE A.QuestionnaireAnswerId = QA.Id    
     FOR XML PATH('Answer'), ROOT('Answers'), TYPE    
    )    
    FROM QuestionnaireAnswer QA     
     INNER JOIN QuestionnaireVersion QV ON QV.Id = QA.QuestionnaireVersionId    
     INNER JOIN Questionnaire Q ON Q.Id = QV.QuestionnaireId    
     INNER JOIN COI_QuestionnaireAnswer_Map MAP ON MAP.QuestionnaireAnswerId = QA.Id    
    WHERE MAP.COIId = COI.Id    
    AND Q.[Name] IN ('Your Equity Interests', 'Trustee Association')    
    FOR XML PATH('EquityInterest'), ROOT('EquityInterests'), TYPE    
   ),    
   (     
    SELECT QA.Name, QA.Highlight,    
    (    
     SELECT A.[Name], A.[Value], (SELECT [IO].OptionText     
             FROM ItemOption [IO]    
              INNER JOIN QuestionnaireItem QI ON QI.Id = [IO].ItemId    
             WHERE QI.VersionId = QV.Id    
              AND QI.ItemName = A.[Name]    
              AND [IO].OptionValue = A.[Value]) AS [Text],    
            (SELECT RefMeaning    
             FROM RefCode    
             WHERE RefCode.RefCode = A.Value) AS [RefMeaning]    
     FROM Answer A     
     WHERE A.QuestionnaireAnswerId = QA.Id    
     FOR XML PATH('Answer'), ROOT('Answers'), TYPE    
    )    
    FROM QuestionnaireAnswer QA     
     INNER JOIN QuestionnaireVersion QV ON QV.Id = QA.QuestionnaireVersionId    
     INNER JOIN Questionnaire Q ON Q.Id = QV.QuestionnaireId    
     INNER JOIN COI_QuestionnaireAnswer_Map MAP ON MAP.QuestionnaireAnswerId = QA.Id    
    WHERE MAP.COIId = COI.Id    
    AND Q.[Name] IN ('Your Family''s Associations and Equity Interests', 'Trustee''s Family Associations and Equity Interests')    
    FOR XML PATH('FamilyAssociation'), ROOT('FamilyAssociations'), TYPE    
   ), 
   (     
    SELECT QA.Name, QA.Highlight,    
    (    
     SELECT A.[Name], A.[Value], (SELECT [IO].OptionText     
             FROM ItemOption [IO]    
              INNER JOIN QuestionnaireItem QI ON QI.Id = [IO].ItemId    
             WHERE QI.VersionId = QV.Id    
              AND QI.ItemName = A.[Name]    
              AND [IO].OptionValue = A.[Value]) AS [Text],    
            (SELECT RefMeaning    
             FROM RefCode    
             WHERE RefCode.RefCode = A.Value) AS [RefMeaning]    
     FROM Answer A     
     WHERE A.QuestionnaireAnswerId = QA.Id    
     FOR XML PATH('Answer'), ROOT('Answers'), TYPE    
    )    
    FROM QuestionnaireAnswer QA     
     INNER JOIN QuestionnaireVersion QV ON QV.Id = QA.QuestionnaireVersionId    
     INNER JOIN Questionnaire Q ON Q.Id = QV.QuestionnaireId    
     INNER JOIN COI_QuestionnaireAnswer_Map MAP ON MAP.QuestionnaireAnswerId = QA.Id    
    WHERE MAP.COIId = COI.Id    
    AND Q.[Name] = 'Your Intellectual Property'    
    FOR XML PATH('Royalty'), ROOT('Royalties'), TYPE    
   )    
 FROM conflictofinterest COI    
  INNER JOIN AdmPerson ADMP ON COI.AdmPersonId = ADMP.Id    

 WHERE COI.Id = @coiId    
 FOR XML PATH('COIReport'), ROOT('COIReports'), TYPE    
     
END    


